//------------------------------------------------------------------------------
// <copyright company="Telligent Systems">
//     Copyright (c) Telligent Systems Corporation.  All rights reserved.
// </copyright> 
//------------------------------------------------------------------------------

using System;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Web.Mail;

namespace CommunityServer.Components
{
	/// <summary>
	/// Summary description for XmlEmailTemplateProvider.
	/// </summary>
	public class SqlEmailQueueProvider : EmailQueueProvider
	{
		#region Member variables
		protected string databaseOwner	= "dbo";	// overwrite in web.config
		string connectionString = null;
		ProviderHelper sqlHelper = null;
		#endregion

		#region Constructor
		/****************************************************************
		// SqlDataProvider
		//
		/// <summary>
		/// Class constructor
		/// </summary>
		//
		****************************************************************/
		public SqlEmailQueueProvider(string databaseOwner, string connectionString) 
		{

			// Read the connection string for this provider
			//
			this.connectionString = connectionString;

			// Read the database owner name for this provider
			//
			this.databaseOwner = databaseOwner;

			sqlHelper = ProviderHelper.Instance();
		}
		#endregion
	
		#region SettingsID 
		protected int GetSettingsID()
		{
			return sqlHelper.GetSettingsID();
		}
		protected SqlParameter SettingsIDParameter()
		{
			SqlParameter p = new SqlParameter("@SettingsID",SqlDbType.Int);
			p.Value = GetSettingsID();
			return p;
		}
		#endregion

		#region Helpers
		protected SqlConnection GetSqlConnection () 
		{
			try { return new SqlConnection(ConnectionString); }
			catch { throw new CSException(CSExceptionType.DataProvider, "SQL Connection String is invalid."); }
		}

		public string ConnectionString 
		{
			get { return connectionString; }
			set { connectionString = value; }
		}
		#endregion

		/// <summary>
		/// A method to add an email message to the cs_EmailQueue which will be processed later by a background thread
		/// which will actually send the email message
		/// </summary>
		/// <param name="message">The email message to send</param>
		public override void QueueEmail(MailMessage message)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Emails_Enqueue", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;

				myCommand.Parameters.Add(this.SettingsIDParameter());

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@EmailTo", SqlDbType.NVarChar, 2000).Value = message.To;

				if (message.Cc != null)
					myCommand.Parameters.Add("@EmailCc", SqlDbType.NText).Value = message.Cc;
				else
					myCommand.Parameters.Add("@EmailCc", SqlDbType.NText).Value = DBNull.Value;

				if (message.Bcc != null)
					myCommand.Parameters.Add("@EmailBcc", SqlDbType.NText).Value = message.Bcc;
				else
					myCommand.Parameters.Add("@EmailBcc", SqlDbType.NText).Value = DBNull.Value;

				myCommand.Parameters.Add("@EmailFrom", SqlDbType.NVarChar, 256).Value = message.From;
				myCommand.Parameters.Add("@EmailSubject", SqlDbType.NVarChar, 1024).Value = message.Subject;
				myCommand.Parameters.Add("@EmailBody", SqlDbType.NText).Value = message.Body;
				myCommand.Parameters.Add("@EmailPriority", SqlDbType.Int).Value = (int) message.Priority;
				myCommand.Parameters.Add("@EmailBodyFormat", SqlDbType.Int).Value = (int) message.BodyFormat;
    
				// Execute the command
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
		}

		/// <summary>
		/// A method called by the framework to retrieve email messages from the cs_EmailQueue table.
		/// </summary>
		/// <returns>An ArrayList of MailMessage(s) that will need to be sent.</returns>
		public override ArrayList DequeueEmail(int settingsID)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_Emails_Dequeue", myConnection);
				myCommand.CommandType = CommandType.StoredProcedure;
				myCommand.Parameters.Add("@SettingsID",SqlDbType.Int,4).Value = settingsID;
				ArrayList emails = new ArrayList();
				SqlDataReader reader;

				// Execute the command
				myConnection.Open();

				using(reader = myCommand.ExecuteReader(CommandBehavior.CloseConnection))
				{
					while (reader.Read()) 
					{
						emails.Add(PopulateEmailFromIDataReader(reader));
					}
					reader.Close();
				}

				myConnection.Close();

				return emails;
			}
		}

		/// <summary>
		/// A method to remove email messages from the cs_EmailQueue table that were successfully sent.
		/// </summary>
		/// <param name="emailID">The EmailID (Guid) that was successfully sent.</param>
		public override void DeleteQueuedEmail(Guid emailID)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_EmailQueue_Delete", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@EmailID", SqlDbType.UniqueIdentifier).Value = emailID;
				myConnection.Open();
				myCommand.ExecuteNonQuery();
				myConnection.Close();
			}
		}

		/// <summary>
		/// A method to updated failed email messages int the cs_EmailQueue table that were not sent.
		/// </summary>
		/// <param name="list">The list of EmailIDs (Guids) that were not sent.</param>
		/// <param name="failureInterval">The number of minutes before attempting again.  This is scaled against the number of tries.</param>
		public override void QueueSendingFailure (ArrayList list, int failureInterval, int maxNumberOfTries)
		{
			using( SqlConnection myConnection = GetSqlConnection() ) 
			{
				SqlCommand myCommand = new SqlCommand(databaseOwner + ".cs_EmailQueue_Failure", myConnection);

				// Mark the Command as a SPROC
				myCommand.CommandType = CommandType.StoredProcedure;

				// Add Parameters to SPROC
				myCommand.Parameters.Add("@EmailID", SqlDbType.UniqueIdentifier );
				myCommand.Parameters.Add("@FailureInterval", SqlDbType.Int).Value = failureInterval;
				myCommand.Parameters.Add("@MaxNumberOfTries", SqlDbType.Int).Value = maxNumberOfTries;
				myConnection.Open();

				for (int i = 0; i < list.Count; i++) 
				{
					myCommand.Parameters[0].Value = (Guid)list[i];
					myCommand.ExecuteNonQuery();
				}

				myConnection.Close();
			}
		}
	
	}
}
